/**
 * 
 */
package cn.bblocks.cacheTest.suport;

/** 
* @version:v1.0
* @Description: 
* @author: jzhao
* @date: 2019年12月3日上午10:59:48
*/

import java.sql.Connection;  
import java.sql.DriverManager;  
import java.sql.ResultSet;  
import java.sql.SQLException;  
import java.sql.Statement;  
import java.util.Properties;

import cn.hutool.core.util.RandomUtil;  
  
/** 
 * @author greatwqs 
 * @date 2011-8-12 
 */  
public class DerbyTest_File {  
  
	private final static String DB_URL = "jdbc:derby:memory;create=true";  
//    private final static String DB_URL = "jdbc:derby:c:\\data\\ij_cmd_test_db;create=true";  
    private final static String DERBY_DRIVER = "org.apache.derby.jdbc.EmbeddedDriver";  
  
    public static void main(String[] args) {  
        Connection conn = null;  
        try {  
            Class.forName(DERBY_DRIVER);  
            Properties properties = new Properties();  
             properties.put("create", "false"); // 新建数据库  
             properties.put("user", "APP");     // 用户名  
             properties.put("password", "APP"); // 密码  
  
            // properties.put("retreiveMessagesFromServerOnGetMessage", "true");  
            conn = DriverManager.getConnection(DB_URL, properties);  
  
            Statement stat = conn.createStatement();  
            
            //stat.executeUpdate("drop table Greetings");
            
            stat.executeUpdate("CREATE TABLE Greetings (Message char(64)  )");
            stat.executeUpdate("CREATE INDEX tbl_time_create ON Greetings(Message)");
            
            
			long b = System.currentTimeMillis();
			int total = 10*10000;
			String add = null;
			
			StringBuilder sb = new StringBuilder();
			int count = 0;
			for(int i=0;i<total;i++) {
				add = "UserBank::&userId="+RandomUtil.randomInt(10000000)+"&page="+RandomUtil.randomInt(100)+"&size="+RandomUtil.randomInt(10);
				
				if(sb.length() != 0) {
					sb.append(",");
				}
				sb.append("('"+add+"')");
				count++;
				try {
					//stat.executeUpdate("INSERT INTO Greetings VALUES ('"+add+"')");
					if(count>=10) {
						///System.out.println("INSERT INTO Greetings VALUES "+sb.toString());
						stat.executeLargeUpdate("INSERT INTO Greetings VALUES "+sb.toString());
						
						count = 0;
						sb = new StringBuilder();
					}
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
            System.out.println("cost:"+(System.currentTimeMillis() - b));
            
            
            add = add.substring(0, add.indexOf("&", add.indexOf("&")+1))+"%";
            System.out.println(add);
            
            b = System.currentTimeMillis();
            
            total = 100;
            for(int i=0;i<total;i++) {
            	ResultSet result = null;
            	if(i == 0)
	              result = stat.executeQuery("SELECT * FROM Greetings where Message like '"+add+"'");  
            	else {
    				add = "UserBank::&userId="+RandomUtil.randomInt(10000000)+"&%";
      	             result = stat.executeQuery("SELECT * FROM Greetings where Message like '"+add+"'");  
            	}

	            while (result.next()) {  
	                //System.out.println(i+"序号 : " + result.getString(1));  
	            }  
	            
	            
	            result.close(); 
            }
            System.out.println("cost:"+(System.currentTimeMillis() - b));
            
            b = System.currentTimeMillis();
            stat.executeUpdate("drop table Greetings");
            System.out.println("cost:"+(System.currentTimeMillis() - b));
            
            stat.close();  
            conn.close();  
        } catch (ClassNotFoundException e) {  
            e.printStackTrace();  
        } catch (SQLException e) {  
            e.printStackTrace();  
        } finally {  
            // 内嵌模式数据库操作用完之后需要关闭数据库,这里没有执行数据库名称则全部关闭.  
            try {  
                DriverManager.getConnection("jdbc:derby:;shutdown=true");  
            } catch (SQLException e) {  
                e.getMessage();  
            }  
        }  
    }  
}  
